﻿using QuanLyGiaoVuDAL.ADOWrapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using QuanLyGiaoVuDAL.Mapper;
using QuanLyGiaoVuDAL.DTO;
using System.Data;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class MonHocRepository : IMonHocRepository
    {
        public DTO.MonHocDTO GetMonHocById(Guid id)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_MONHOCSelect]", new SqlParameter("@ID", id));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToMonHocDTO();
        }

        public IEnumerable<DTO.MonHocDTO> GetAllMonHoc(Guid? maLop = null)
        {
            List<MonHocDTO> monhocList = new List<MonHocDTO>();
            SqlParameter idParameter = DBParameterFactory.Create("ID", SqlDbType.UniqueIdentifier, DBNull.Value);
            SqlParameter maLopParameter = DBParameterFactory.Create("LopSinhVien", SqlDbType.UniqueIdentifier, maLop);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_MonHocSelect]", idParameter, maLopParameter);
            var monHocTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in monHocTable)
            {
                monhocList.Add(dataRow.ToMonHocDTO());
            }
            return monhocList.AsEnumerable();
        }

        public DTO.MonHocDTO InsertMonHoc(DTO.MonHocDTO monHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_MonHocInsert]", GetParameterArray(monHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToMonHocDTO();
        }

        public DTO.MonHocDTO UpdateMonHoc(DTO.MonHocDTO monHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_MONHOCUpdate]", GetParameterArray(monHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToMonHocDTO();
        }

        public bool DeleteMonHoc(Guid id)
        {
            throw new NotImplementedException();
        }

#region Private Methods
        private SqlParameter[] GetParameterArray(MonHocDTO monHocDTO)
        {
            SqlParameter[] parameters = new SqlParameter[7];
            parameters[0] = new SqlParameter("@Id", monHocDTO.ID);
            parameters[1] = new SqlParameter("@MaMonHoc", monHocDTO.MaMonHoc);
            parameters[2] = new SqlParameter("@TenMonHoc", monHocDTO.TenMonHoc);
            parameters[3] = new SqlParameter("@SoTinChi", monHocDTO.TongSoTinChi);
            parameters[4] = new SqlParameter("@SoTinChiLyThuyet", monHocDTO.SoTinChiLyThuyet);
            parameters[5] = new SqlParameter("@HocKy", monHocDTO.HocKy);
            parameters[6] = monHocDTO.MaMonChinh.HasValue ? new SqlParameter("@MaMonChinh", monHocDTO.MaMonChinh.Value) : DBParameterFactory.Create("MaMonChinh", SqlDbType.UniqueIdentifier, DBNull.Value);
            if (monHocDTO.MaMonChinh.Equals(Guid.Empty))
            {
                parameters[6].DbType = DbType.Guid;
                parameters[6].Value = DBNull.Value;
            }
            return parameters;
        }

        private SqlParameter[] GetParameterArray(Guid maLop, Guid maMonHoc, int hocKy)
        {
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = DBParameterFactory.Create("MaLop", maLop);
            parameters[1] = DBParameterFactory.Create("MaMonHoc", maMonHoc);
            parameters[2] = DBParameterFactory.Create("HocKyThu", hocKy);
            return parameters;
        }

        private SqlParameter[] GetParameterArray(Guid maLop, Guid maMonHoc)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("MaLop", maLop);
            parameters[1] = DBParameterFactory.Create("MaMonHoc", maMonHoc);
            return parameters;
        }

        private SqlParameter[] GetParameterArray(Guid maMonHoc, int maBoMon)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("MaMonHoc", maMonHoc);
            parameters[1] = DBParameterFactory.Create("MaBoMon", maBoMon);
            return parameters;
        }
#endregion


        public IEnumerable<GiangVienDTO> GetAllGiangVienByMonHocId(Guid id)
        {
            SqlParameter giangVienIdParam = DBParameterFactory.Create("GiangVienId", SqlDbType.UniqueIdentifier, DBNull.Value);
            SqlParameter monHocIdParam = DBParameterFactory.Create("MonHocId", id);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_KHANANGSelect]", giangVienIdParam, monHocIdParam);
            var tableResult = dbResult.Tables[0].Rows;

            List<GiangVienDTO> danhSachGiangVien = new List<GiangVienDTO>();

            foreach (DataRow dataRow in tableResult)
            {
                var giangVien = dataRow.ParseToGiangVienDTO();
                if (giangVien != null)
                {
                    danhSachGiangVien.Add(giangVien);
                }
            }
            return danhSachGiangVien.AsEnumerable();
        }


        public bool InsertMonHocChuongTrinhKhung(Guid maLop, Guid maMonHoc, int hocKy)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CHUONGTRINHKHUNGInsert]", GetParameterArray(maLop, maMonHoc, hocKy));
                var dataRow = dbResult.Tables[0].Rows[0];
                return true;
            }
            catch
            {
                return false;
            }
        }


        public bool DeleteMonHocChuongTrinhKhung(Guid maLop, Guid maMonHoc)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CHUONGTRINHKHUNGDelete]", GetParameterArray(maLop, maMonHoc));
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool InsertMonHocBoMon(Guid maMonHoc, int maBoMon)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CT_BOMONInsert]", GetParameterArray(maMonHoc, maBoMon));
                var dataRow = dbResult.Tables[0].Rows[0];
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool DeleteMonHocBoMon(Guid maMonHoc, int maBoMon)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CT_BOMONDelete]", GetParameterArray(maMonHoc, maBoMon));
                var dataRow = dbResult.Tables[0].Rows[0];
                return true;
            }
            catch
            {
                return false;
            }
        }

        public IEnumerable<MonHocDTO> GetAllMonHocByBoMon(int maBoMon)
        {
            List<MonHocDTO> monhocList = new List<MonHocDTO>();
            SqlParameter maMonHocParameter = DBParameterFactory.Create("MaMonHoc", SqlDbType.UniqueIdentifier, DBNull.Value);
            SqlParameter maBoMonParameter = DBParameterFactory.Create("MaBoMon", SqlDbType.Int, maBoMon);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_CT_BOMONSelect]", maMonHocParameter, maBoMonParameter);
            var monHocTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in monHocTable)
            {
                monhocList.Add(dataRow.ParseCTBoMonToMonHocDTO());
            }
            return monhocList.AsEnumerable();
        }
    }
}
